package org.example.managementsystem.repository;

import org.example.managementsystem.model.MaterialPurchase;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Objects;

@Repository
public class MaterialPurchaseDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public MaterialPurchaseDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 添加物料采购申请
     */
    public Long insertMaterialPurchase(MaterialPurchase purchase) {
        String sql = "INSERT INTO material_purchase_application " +
                "(material_name, material_type, provider_name, factory_address, storage_date, " +
                "specification, contact_person, contact_phone, material_desc, purchase_quantity, " +
                "applicant_job_number, purchase_desc, is_approved) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, purchase.getMaterialName());
            ps.setString(2, purchase.getMaterialType());
            ps.setString(3, purchase.getProviderName());
            ps.setString(4, purchase.getFactoryAddress());
            ps.setObject(5, purchase.getStorageDate());
            ps.setString(6, purchase.getSpecification());
            ps.setString(7, purchase.getContactPerson());
            ps.setString(8, purchase.getContactPhone());
            ps.setString(9, purchase.getMaterialDesc());
            ps.setInt(10, purchase.getPurchaseQuantity());
            ps.setString(11, purchase.getApplicantJobNumber());
            ps.setString(12, purchase.getPurchaseDesc());
            ps.setBoolean(13, purchase.getIsApproved());
            return ps;
        }, keyHolder);

        return Objects.requireNonNull(keyHolder.getKey()).longValue();
    }

    /**
     * 更新物料采购申请
     */
    public int updateMaterialPurchase(MaterialPurchase purchase) {
        String sql = "UPDATE material_purchase_application SET " +
                "material_name = ?, material_type = ?, provider_name = ?, factory_address = ?, " +
                "storage_date = ?, specification = ?, contact_person = ?, contact_phone = ?, " +
                "material_desc = ?, purchase_quantity = ?, purchase_desc = ?, is_approved = ? " +
                "WHERE purchase_id = ?";

        return jdbcTemplate.update(sql,
                purchase.getMaterialName(),
                purchase.getMaterialType(),
                purchase.getProviderName(),
                purchase.getFactoryAddress(),
                purchase.getStorageDate(),
                purchase.getSpecification(),
                purchase.getContactPerson(),
                purchase.getContactPhone(),
                purchase.getMaterialDesc(),
                purchase.getPurchaseQuantity(),
                purchase.getPurchaseDesc(),
                purchase.getIsApproved(),
                purchase.getPurchaseId());
    }

    /**
     * 更新采购申请审批状态
     */
    public int updatePurchaseApprovalStatus(Long purchaseId, Boolean isApproved, String description) {
        String sql = "UPDATE material_purchase_approval SET " +
                "is_approved = ?, description = ? " +
                "WHERE purchase_id = ?";

        return jdbcTemplate.update(sql, isApproved, description, purchaseId);
    }

    /**
     * 删除物料采购申请
     */
    public int deleteMaterialPurchase(Long purchaseId) {
        // 先删除关联的审批记录
        String deleteApprovalSql = "DELETE FROM material_purchase_approval WHERE purchase_id = ?";
        jdbcTemplate.update(deleteApprovalSql, purchaseId);

        // 再删除采购申请
        String deleteApplicationSql = "DELETE FROM material_purchase_application WHERE purchase_id = ?";
        return jdbcTemplate.update(deleteApplicationSql, purchaseId);
    }

    /**
     * 根据ID查询物料采购申请
     */
    public MaterialPurchase getMaterialPurchaseById(Long purchaseId) {
        String sql = "SELECT a.*, ap.is_approved AS approval_status, ap.description AS approval_description, p.department " +
                "FROM material_purchase_application a " +
                "LEFT JOIN material_purchase_approval ap ON a.purchase_id = ap.purchase_id " +
                "LEFT JOIN person p ON a.applicant_job_number = p.job_number " +
                "WHERE a.purchase_id = ?";

        List<MaterialPurchase> purchases = jdbcTemplate.query(sql, new MaterialPurchaseRowMapper(), purchaseId);
        return purchases.isEmpty() ? null : purchases.get(0);
    }

    /**
     * 查询所有物料采购申请
     */
    public List<MaterialPurchase> getAllMaterialPurchases() {
        String sql = "SELECT a.*, ap.is_approved AS approval_status, ap.description AS approval_description, p.department " +
                "FROM material_purchase_application a " +
                "LEFT JOIN material_purchase_approval ap ON a.purchase_id = ap.purchase_id " +
                "LEFT JOIN person p ON a.applicant_job_number = p.job_number " +
                "ORDER BY a.purchase_id DESC";
        return jdbcTemplate.query(sql, new MaterialPurchaseRowMapper());
    }

    /**
     * 查询指定申请人的物料采购申请
     */
     public List<MaterialPurchase> getMaterialPurchasesByApplicant(String applicantJobNumber) {
        String sql = "SELECT a.*, ap.is_approved AS approval_status, ap.description AS approval_description, p.department " +
                "FROM material_purchase_application a " +
                "LEFT JOIN material_purchase_approval ap ON a.purchase_id = ap.purchase_id " +
                "LEFT JOIN person p ON a.applicant_job_number = p.job_number " +
                "WHERE a.applicant_job_number = ? " +
                "ORDER BY a.purchase_id DESC";
        return jdbcTemplate.query(sql, new MaterialPurchaseRowMapper(), applicantJobNumber);
    }

    /**
     * 获取待审批的采购申请列表
     */
    public List<MaterialPurchase> getPendingApprovals() {
        String sql = "SELECT a.*, ap.is_approved AS approval_status, ap.description AS approval_description, p.department " +
                "FROM material_purchase_application a " +
                "LEFT JOIN material_purchase_approval ap ON a.purchase_id = ap.purchase_id " +
                "LEFT JOIN person p ON a.applicant_job_number = p.job_number " +
                "WHERE ap.purchase_id IS NULL " +
                "ORDER BY a.purchase_id DESC";
        return jdbcTemplate.query(sql, new MaterialPurchaseRowMapper());
    }

    /**
     * 插入或更新审批记录
     */
    public int insertOrUpdateApproval(Long purchaseId, Boolean isApproved, String description) {
        // 先检查是否已存在审批记录
        String checkSql = "SELECT COUNT(*) FROM material_purchase_approval WHERE purchase_id = ?";
        int count = jdbcTemplate.queryForObject(checkSql, Integer.class, purchaseId);
        
        if (count > 0) {
            // 更新现有记录
            String updateSql = "UPDATE material_purchase_approval SET is_approved = ?, description = ? WHERE purchase_id = ?";
            return jdbcTemplate.update(updateSql, isApproved, description, purchaseId);
        } else {
            // 插入新记录
            String insertSql = "INSERT INTO material_purchase_approval (purchase_id, is_approved, description) VALUES (?, ?, ?)";
            return jdbcTemplate.update(insertSql, purchaseId, isApproved, description);
        }
    }

    /**
     * 更新申请表的审批状态
     */
    public int updateApplicationApprovalStatus(Long purchaseId, Boolean isApproved) {
        String sql = "UPDATE material_purchase_application SET is_approved = ? WHERE purchase_id = ?";
        return jdbcTemplate.update(sql, isApproved, purchaseId);
    }

    public void deleteById(Long id) {
        // 删除审批记录（如果有）
        String deleteApprovalSql = "DELETE FROM material_purchase_approval WHERE purchase_id = ?";
        jdbcTemplate.update(deleteApprovalSql, id);

        // 删除采购申请记录
        String deleteApplicationSql = "DELETE FROM material_purchase_application WHERE purchase_id = ?";
        jdbcTemplate.update(deleteApplicationSql, id);
    }

    /**
     * 物料采购申请行映射器
     */
    private static class MaterialPurchaseRowMapper implements RowMapper<MaterialPurchase> {
        @Override
        public MaterialPurchase mapRow(ResultSet rs, int rowNum) throws SQLException {
            MaterialPurchase purchase = new MaterialPurchase();
            purchase.setPurchaseId(rs.getLong("purchase_id"));
            purchase.setMaterialName(rs.getString("material_name"));
            purchase.setMaterialType(rs.getString("material_type"));
            purchase.setProviderName(rs.getString("provider_name"));
            purchase.setFactoryAddress(rs.getString("factory_address"));
            purchase.setStorageDate(rs.getDate("storage_date"));
            purchase.setSpecification(rs.getString("specification"));
            purchase.setContactPerson(rs.getString("contact_person"));
            purchase.setContactPhone(rs.getString("contact_phone"));
            purchase.setMaterialDesc(rs.getString("material_desc"));
            purchase.setPurchaseQuantity(rs.getInt("purchase_quantity"));
            purchase.setApplicantJobNumber(rs.getString("applicant_job_number"));
            purchase.setPurchaseDesc(rs.getString("purchase_desc"));
            purchase.setIsApproved(rs.getBoolean("is_approved"));
            purchase.setDepartment(rs.getString("department"));

            // 审批信息
            purchase.setApprovalStatus(rs.getBoolean("approval_status"));
            purchase.setApprovalDescription(rs.getString("approval_description"));

            return purchase;
        }
    }
}